﻿namespace nCodeGeneratingTool
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;

    public class DALHelper
    {
        #region Fields

        public SqlConnection _Connection = null;

        #endregion Fields

        #region Constructors

        public DALHelper(string connectionString)
        {
            _Connection = new SqlConnection(connectionString);
        }

        #endregion Constructors

        #region Methods

        public static DataTable GetDatatable(List<string> dataList, string tableName, List<string> columnList)
        {
            DataTable dt;
            dt = new DataTable(tableName);

            foreach (string column in columnList)
            {
                dt.Columns.Add(column, typeof(string));
            }

            foreach (string item in dataList)
            {
                dt.Rows.Add(item);
            }

            return dt;
        }

        public static DataTable GetDataTableOneColumn(List<object> dataList, string tableName, string columnName, Type columnType)
        {
            DataTable dt;
            dt = new DataTable(tableName);
            dt.Columns.Add(columnName, columnType);

            foreach (object item in dataList)
            {
                dt.Rows.Add(item);
            }

            return dt;
        }

        public static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, object parameterValue)
        {
            SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType);
            paramParameter.Value = parameterValue;
            return paramParameter;
        }

        public void Dispose()
        {
            try
            {
                if (_Connection != null && _Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
        {
            int returnValue = 0;
            try
            {
                Open();
                SqlCommand oCommand = new SqlCommand(query, _Connection);
                if (parameters != null)
                    oCommand.Parameters.AddRange(parameters);

                returnValue = oCommand.ExecuteNonQuery();
                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }

            return returnValue;
        }

        public int ExecuteNonQueryProcedure(string storedProcName, params SqlParameter[] parameters)
        {
            int returnValue = 0;
            try
            {
                Open();
                SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
                returnValue = oCommand.ExecuteNonQuery();
                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }

            return returnValue;
        }

        public DataSet ExecuteProcedureReader(string storedProcName, params SqlParameter[] parameters)
        {
            DataSet Result = new DataSet();
            SqlDataAdapter objSqlDataAdapter = null;
            try
            {
                Open();
                SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
                oCommand.CommandTimeout = 240;
                objSqlDataAdapter = new SqlDataAdapter(oCommand);
                objSqlDataAdapter.Fill(Result);
                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            return Result;
        }

        public DataSet ExecuteQueryReader(string query, params SqlParameter[] parameters)
        {
            DataSet Result = new DataSet();
            SqlDataAdapter objSqlDataAdapter = null;
            try
            {
                Open();

                SqlCommand oCommand = new SqlCommand(query, _Connection);

                if (parameters != null)
                    oCommand.Parameters.AddRange(parameters);

                objSqlDataAdapter = new SqlDataAdapter(oCommand);
                objSqlDataAdapter.Fill(Result);

                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            return Result;
        }

        public int ExecutScalarProcedure(string storedProcName, params SqlParameter[] parameters)
        {
            int returnValue = 0;
            try
            {
                Open();
                SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
                returnValue = Convert.ToInt32(oCommand.ExecuteScalar());
                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }

            return returnValue;
        }

        public string ExecutScalarProcedureString(string storedProcName, params SqlParameter[] parameters)
        {
            string returnValue = string.Empty;
            try
            {
                Open();
                SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
                returnValue = oCommand.ExecuteScalar().ToString();
                oCommand.Dispose();
                Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }

            return returnValue;
        }

        public SqlCommand GetSqlCommandObject(string storedProcName, params SqlParameter[] parameters)
        {
            SqlCommand oCommand = new SqlCommand(storedProcName, _Connection);
            oCommand.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                oCommand.Parameters.AddRange(parameters);
            return oCommand;
        }

        public void Open()
        {
            try
            {
                if (_Connection != null && _Connection.State == ConnectionState.Closed)
                    _Connection.Open();
            }
            catch (Exception e)
            {
            }
        }

        internal static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, int parameterSize, string parameterValue)
        {
            SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType, parameterSize);
            paramParameter.Value = parameterValue;
            return paramParameter;
        }

        #endregion Methods
    }
}